from db_interface import db_interface
from scipy import stats
from public_func.exchange_date import cal_stock_exchange_date
from public_func.register_email import Mail
import datetime
import json
from collections import Counter



def stock_mkv_data(mkv=300):
    sql = "select code, name, mkv, pe_ttm, ids from stock_info where mkv >= {}".format(mkv)
    query = db_interface.stock_base.select(sql)
    mkv_dict = {}
    for item in query:
        code, name, mkv, pe_ttm, ids = item
        mkv_dict[code] = name, mkv, pe_ttm, ids
    return mkv_dict



def stock_price_percent():
    date_inter = cal_stock_exchange_date(31)
    sql = "select code, date, now from stock_days_sh where date between '{}' and '{}' union " \
          "select code, date, now from stock_days_sz where date between '{}' and '{}' " \
          "order by code, date desc".format(date_inter[0], date_inter[1], date_inter[0], date_inter[1])
    query = db_interface.stock_base.select(sql)
    stock_dict = {}
    for item in query:
        name, date, now = item
        date = date.strftime('%Y-%m-%d')
        if name not in stock_dict:
            stock_dict[name] = []
        stock_dict[name].append((date, now))

    mkv_dict = stock_mkv_data(mkv=450)
    result = []
    stock_result = []
    for k, v in stock_dict.items():
        price_list = [x[1] for x in v]
        price_per = stats.percentileofscore(price_list, price_list[0])
        if price_per <= 7.5:
            percent = '%.1f' % price_per
            if k in mkv_dict:
                name, mkv = mkv_dict[k]
                stock_result.append(name)
                change = 100*price_list[0]/price_list[-1] - 100
                result.append(
                    {
                        'name': name,
                        'price': price_list[0],
                        'percent': percent,
                        'down': round(change, 2),
                        'mkv': mkv
                    }
                )
    sql = "select record_date from remind_record where record_date >= '{}' and " \
          "type = 'chance'".format(date_inter[0])
    query = db_interface.stock_base.select(sql)
    date_list = []
    for item in query:
        date_list.append(item[0].strftime('%Y-%m-%d'))
    today = datetime.datetime.now().strftime('%Y-%m-%d')
    if today not in date_list:
        data_dict = {
            'type': 'chance', 'stock': '、'.join(stock_result),
        }
        db_interface.stock_base.insert_dict('remind_record', data_dict)
    sql_time = "select stock from remind_record where type = 'chance' " \
               "order by record_date desc limit 5".format(
        date_inter[1]
    )
    query_time = db_interface.stock_base.select(sql_time)
    time_list = []
    for item in query_time:
        time_list += item[0].split('、')
    stock_counter = Counter(time_list)
    result.sort(key=lambda x: x['down'])

    new_result = []
    for item in result:
        word = "%s：%s元，%s分位，下跌：%s%%，市值：%s亿，提醒：%s次；" % \
               (item['name'], item['price'], item['percent'], item['down'], item['mkv'],
                stock_counter[item['name']])
        new_result.append(
            word
        )

    return new_result


## 添加大盘百分位
def sh_index_percent():
    date_inter = cal_stock_exchange_date(31)
    sql = "select  date, now from sh_days_data where date between '{}' and '{}' " \
          "order by code, date desc".format(date_inter[0], date_inter[1])
    query = db_interface.stock_base.select(sql)
    index_list = []
    for item in query:
        date, now = item
        date = date.strftime('%Y-%m-%d')
        index_list.append((date, now))

    price_list = [x[1] for x in index_list]
    price_per = stats.percentileofscore(price_list, price_list[0])
    change = 100*price_list[0]/price_list[-1] - 100

    result = {
            'price': price_list[0],
            'percent': '%.1f' % price_per,
            'change': round(change, 2),
        }
    summary = '大盘{}，{}百分位，波动{}。'.format(
        result['price'], result['percent'], result['change']
    )
    return summary




def have_stock_pe_percent(stock_list=['002142', '600048', '600276', '300059', '600009', '601336', '603993','600900']):
    date_start, date_end = cal_stock_exchange_date(60)
    sh_stock = []
    sz_stock = []
    for stock in stock_list:
        if stock.startswith('6'):
            sh_stock.append(stock)
        else:
            sz_stock.append(stock)
    sh_word = f'in {tuple(sh_stock)}' if len (sh_stock) >= 2 else f'={sh_stock[0]}' if sh_stock else ''
    sql_sh = f"select stock, date, pe_ttm from stock_pe_sh where " \
             f"date between '{date_start}' and '{date_end}' and " \
             f"stock {sh_word} order by stock, date desc"
    query_sh = list(db_interface.stock_base.select(sql_sh))

    sz_word = f'in {tuple(sz_stock)}' if len (sz_stock) >= 2 else f'={sz_stock[0]}' if sz_stock else ''
    sql_sz = f"select stock, date, pe_ttm from stock_pe_sz where " \
             f"date between '{date_start}' and '{date_end}'and " \
             f"stock {sz_word} order by stock, date desc"

    query_sz= list(db_interface.stock_base.select(sql_sz))
    stock_dict = {}
    for item in query_sh + query_sz:
        name, date, now = item
        date = date.strftime('%Y-%m-%d')
        if name not in stock_dict:
            stock_dict[name] = []
        stock_dict[name].append((date, now))

    result = {}
    for k, v in stock_dict.items():
        price_list = [x[1] for x in v]
        stock_pe = price_list[0]
        price_per = stats.percentileofscore(price_list, stock_pe)
        percent = '%.1f%%' % price_per
        result[k] = stock_pe, percent

    return result

def stock_name_to_code(stock_name=''):
    sql = f"select code from stock_info where name = '{stock_name}' limit 1"
    query = db_interface.stock_base.select(sql)
    code = ''
    for item in query:
        if item:
            code = item[0]
    return code

## 添加持有股百分位
def have_stock_percent(stock_list=['002142', '600048',], type="code"):
    date_inter = cal_stock_exchange_date(31)
    if type == "name":
        stock_code = stock_name_to_code(stock_list[0])
        stock_list = [stock_code]
    sh_stock = [x for x in stock_list if x.startswith('6')]
    if len(sh_stock) >= 2:
        sql_sh = "select code, date, now from stock_days_sh where date between '{}' and '{}' " \
             "and code in {} order by code, date desc".format(date_inter[0],
                                                              date_inter[1], tuple(sh_stock))
    else:
        sql_sh = "select code, date, now from stock_days_sh where date between '{}' and '{}' " \
                 "and code = '{}' order by code, date desc".format(date_inter[0],
                                                                  date_inter[1], sh_stock[0] if sh_stock else '')
    query_sh = list(db_interface.stock_base.select(sql_sh))
    sz_stock = [x for x in stock_list if not x.startswith('6')]
    if len(sz_stock) >= 2:
        sql_sz = "select code, date, now from stock_days_sz where date between '{}' and '{}' " \
                 "and code in {} order by code, date desc".format(date_inter[0],
                                                              date_inter[1], tuple(sz_stock))
    else:
        sql_sz = "select code, date, now from stock_days_sz where date between '{}' and '{}' " \
                 "and code = '{}' order by code, date desc".format(date_inter[0],
                                                                  date_inter[1], sz_stock[0] if sz_stock else '')

    query_sz= list(db_interface.stock_base.select(sql_sz))
    stock_dict = {}
    for item in query_sh + query_sz:
        name, date, now = item
        date = date.strftime('%Y-%m-%d')
        if name not in stock_dict:
            stock_dict[name] = []
        stock_dict[name].append((date, now))

    mkv_dict = stock_mkv_data(mkv=100)
    result = []
    pe_dict = have_stock_pe_percent(stock_list)
    # print(pe_dict)
    date = None
    for k, v in stock_dict.items():
        price_list = [x[1] for x in v]
        date = v[0][0]
        price_per = stats.percentileofscore(price_list, price_list[0])
        percent = '%.1f%%' % price_per
        if k in mkv_dict:
            name, mkv, _, ids = mkv_dict[k]
            change = 100*price_list[0]/price_list[-1] - 100
            pe, pe_percent = '', ''
            if k in pe_dict:
                pe, pe_percent = pe_dict[k]
            result.append(
                {
                    'name': name,
                    'price': price_list[0],
                    'percent': percent,
                    'change': "%.2f%%" % change,
                    'pe': pe,
                    'pe_percent': pe_percent,
                    'mkv': mkv
                }
            )
    result = sorted(result, key=lambda x: float(x["percent"].replace('%', '')), reverse=True)

    return date, result




def send_percent_mail():
    content_list = stock_price_percent()
    content = '<br>'.join(content_list)
    sh_summary = sh_index_percent()
    have_stock = have_stock_percent()
    have_list = '<br>'.join(have_stock)
    datenow = datetime.datetime.now()
    date = datenow.strftime('%m-%d日')
    mail = Mail(date=date,
                sh=sh_summary,
                have=have_list,
                content=content,
                to_email='1154238897@qq.com')
    mail.send()
    meng_mail = Mail(date=date,
                sh=sh_summary,
                have=have_list,
                content=content,
                to_email='785948710@qq.com')
    # meng_mail.send()

    new_have = have_stock_percent(['600332', '601066', '600195', '000725'])
    new_mail = Mail(date=date,
                sh=sh_summary,
                have='<br>'.join(new_have),
                content=content,
                to_email='cugbdayezi@sina.com')
    new_mail.send()  ## 刘烨

    huang_have = have_stock_percent(['600089', '000875',])
    huang_mail = Mail(date=date,
                sh=sh_summary,
                have='<br>'.join(huang_have),
                content=content,
                to_email='741153389@qq.com')
    huang_mail.send()  ## 黄老师

    guchhui_have = have_stock_percent(['601012', '002594', '600036'])
    guchhui_mail = Mail(date=date,
                      sh=sh_summary,
                      have='<br>'.join(guchhui_have),
                      content=content,
                      to_email='2513628262@qq.com')
    guchhui_mail.send()  ## 晨辉

def sh_percent_data():
    start_date, end_date = cal_stock_exchange_date(31)
    sql_sh = f"select date, now from sh_days_data where date between '{start_date}' and '{end_date}'" \
             f"order by date desc"
    sql_data = list(db_interface.stock_base.select(sql_sh))
    price_list = [x[1] for x in sql_data]
    price = price_list[0]
    price_per = stats.percentileofscore(price_list, price)

    return price, '%.2f'% price_per



if __name__ == "__main__":
    # print(sh_index_percent())
    #
    # send_percent_mail()
    # have_stock_percent()

    stock_list = ["601888", '300015',  '300059', ## 中免、东财、爱尔
                  '000651', '600276',  ## 格力、恒瑞
                  '000001', '600507', ## 平安、方大特钢
                ]
    date, r = have_stock_percent(stock_list)
    weekday = datetime.datetime.strptime(date, "%Y-%m-%d").weekday()
    # r = have_stock_pe_percent(stock_list)
    print('==========')
    print(f"今天是:{date}; 周 {str(weekday+1)}")
    price, percent = sh_percent_data()
    print(f"大盘点位：{price}，百分位：{percent}")
    print('---------')
    for item in r:
        print(item)

    print("我的交易规则是：15%，买入；92以上：卖出！")


    # r = have_stock_pe_percent()
    # print(r)